﻿CREATE PROCEDURE [dbo].[SP_MemberModulesDTO_Select]
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL

, @MemberId Int   =  NULL 


, @ModuleId Int   =  NULL 


, @DisplayName NVarChar (256)  =  NULL 


, @Email NVarChar (256)  =  NULL 


, @ModuleName NVarChar (256)  =  NULL 

	AS
			WITH CTE_MemberModules AS
			( 
			SELECT
 							v.MemberId,
								v.ModuleId,
								v.DisplayName,
								v.Email,
								v.ModuleName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='MemberId' THEN v.MemberId
												WHEN @OrderColume='ModuleId' THEN v.ModuleId
						 
						WHEN @OrderColume='DisplayName' THEN v.DisplayName
						 
						WHEN @OrderColume='Email' THEN v.Email
						 
						WHEN @OrderColume='ModuleName' THEN v.ModuleName
						        	END
			) AS "RowNumber"
  			FROM MemberModules v 
			where 
								(MemberId=@MemberId OR @MemberId Is Null)
										AND(ModuleId=@ModuleId OR @ModuleId Is Null)
										AND(DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(Email=@Email OR @Email Is Null)
										AND(ModuleName=@ModuleName OR @ModuleName Is Null)
								)
			SELECT *
			FROM CTE_MemberModules
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(*) AS TotalRecords 
            FROM MemberModules v
            where 
								(v.MemberId=@MemberId OR @MemberId Is Null)
										AND(v.ModuleId=@ModuleId OR @ModuleId Is Null)
										AND(v.DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(v.Email=@Email OR @Email Is Null)
										AND(v.ModuleName=@ModuleName OR @ModuleName Is Null)
					 	
			)